# -*- coding: utf-8 -*-
import os,stat,shutil,time,sys
import paramiko
import tarfile
import MySQLdb

#   测试机器
#     IP                 用户名           密码            端口号    Root密码
# 114.55.176.184        fnadmin     fn!@#fn123$%^&*     15388   fn%tgb6yhn123
# 120.27.201.238        fnadmin     fn!@#fn123$%^&*     15388   fn%tgb6yhn123
# 120.27.202.5          fnadmin     fn!@#fn123$%^&*     15388   fn%tgb6yhn123

hostname_1='118.178.128.236'
hostname_2='118.178.129.173'

username='fnadmin'
password='fn!@#fn123$%^&*()'
port=15388

#DB_HOSTNAME='118.178.109.155'
DB_HOSTNAME='rm-bp1d63223cpo33211.mysql.rds.aliyuncs.com'
DB_PORT=3306
DB_USERNAME="fnadmin"
DB_PASSWORD="fn!@#fn123tech"
DB_NAME="fn"

# 输出Txt文本
def outputTxt(testResult):
    f = open(u'output.txt', 'a', 0)
    f.truncate()
    for index1 in range(len(testResult)):
        f.write(str(testResult[index1]["userId"])+"\t"\
                +str(testResult[index1]["dropTimes"])+"\t"\
                +str(testResult[index1]["companyId"])+"\t"\
                +str(testResult[index1]["companyName"])+"\t"\
                +str(testResult[index1]["userCode"])+"\t"\
                +str(testResult[index1]["userName"])+"\t\n")
        testResult[index1]["companyName"] = str(testResult[index1]["companyName"]).decode("utf-8")
        testResult[index1]["userName"] = str(testResult[index1]["userName"]).decode("utf-8")
    print str(testResult).replace(" ","")
    f.close()

class userStatistical():
    def __init__(self):
        self.userArray = []
        self.userIdArray = []

    def userAdd(self,userId,dropTimes,companyId="",companyName="",userCode="",userName=""):
        if len(self.userArray)==0:
            newUser = {"userId":str(userId), "dropTimes":str(dropTimes), "companyId":str(companyId), "companyName":companyName, "userCode":userCode, "userName":userName}
            self.userArray.append(newUser)
            self.userIdArray.append(userId)
        else:
            statusIn = False
            for user in self.userArray:
                if(user["userId"]==str(userId)):
                    statusIn = True
                    user["dropTimes"]=str(int(user["dropTimes"])+1)
            if statusIn == False:
                newUser = {"userId":str(userId), "dropTimes":str(dropTimes), "companyId":str(companyId), "companyName":companyName, "userCode":userCode, "userName":userName}
                self.userArray.append(newUser)
                self.userIdArray.append(userId)
                
    def userSum(self,userId,dropTimes,companyId="",companyName="",userCode="",userName=""):
        if len(self.userArray)==0:
            newUser = {"userId":str(userId), "dropTimes":str(dropTimes), "companyId":str(companyId), "companyName":companyName, "userCode":userCode, "userName":userName}
            self.userArray.append(newUser)
            self.userIdArray.append(userId)
        else:
            statusIn = False
            for user in self.userArray:
                if(user["userId"]==userId):
                    statusIn = True
                    user["dropTimes"]=str(int(user["dropTimes"])+int(dropTimes))
            if statusIn == False:
                newUser = {"userId":str(userId), "dropTimes":str(dropTimes), "companyId":str(companyId), "companyName":companyName, "userCode":userCode, "userName":userName}
                self.userArray.append(newUser)
                self.userIdArray.append(userId)

if __name__ == "__main__":
    reportResult = userStatistical()

    #step1: 获取hostname_1上的统计数据
    #print "########################step1: get the data of hostname_1 #####################"
    ssh=paramiko.SSHClient()                                                    #创建SSHClient
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())                   #设置连接的远程主机没有本地主机秘钥或HostKeys对象时的策略
    ssh.connect(hostname=hostname_1,port=port,username=username,password=password)#建立SSHClient连接

    stdin,stdout,stderr=ssh.exec_command("grep '掉线' /data/fnLogs/tcp/netty.log|awk -F ']' '{print $2}'|awk -F '[' '{print $2}'|sort|uniq -c|sort -rn -k1")
    dropInfo = stdout.read()
    dropInfoArray = dropInfo.split('\n')
    for userInfo in dropInfoArray:
        userInfoArray = userInfo.split(" ")
        DropTimes = userInfoArray[len(userInfoArray)-2]
        userId = userInfoArray[len(userInfoArray)-1]
        if(userId==""):
            continue
        reportResult. (userId,DropTimes)

    ssh.close()
    #print(str(reportResult.userArray))
    #print("======================================================================")
    
    #step2: 获取hostname_2上的统计数据
    #print "########################step2: get the data of hostname_2 #####################"
    ssh=paramiko.SSHClient()                                                    #创建SSHClient
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())                   #设置连接的远程主机没有本地主机秘钥或HostKeys对象时的策略
    ssh.connect(hostname=hostname_2,port=port,username=username,password=password)#建立SSHClient连接

    stdin,stdout,stderr=ssh.exec_command("grep '掉线' /data/fnLogs/tcp/netty.log|awk -F ']' '{print $2}'|awk -F '[' '{print $2}'|sort|uniq -c|sort -rn -k1")
    dropInfo = stdout.read()
    dropInfoArray = dropInfo.split('\n')
    for userInfo in dropInfoArray:
        userInfoArray = userInfo.split(" ")
        DropTimes = userInfoArray[len(userInfoArray)-2]
        userId = userInfoArray[len(userInfoArray)-1]
        if(userId==""):
            continue
        reportResult.userSum(userId,DropTimes)

    ssh.close()
    #print(str(reportResult.userArray))
    #print("======================================================================")

    #step3: 查询数据库确认对应数据的企业名称以及用户名称
    #print "########################step3: select the database to get the info of the dropUser #####################"

    conn = MySQLdb.connect(host=DB_HOSTNAME,port=DB_PORT,user=DB_USERNAME,passwd=DB_PASSWORD,db=DB_NAME,charset="utf8")
    cur = conn.cursor()
    sql="SELECT aa._user_id,aa._user_name,aa._user_code,bb._company_id,bb._company_name FROM fn_company_member aa LEFT JOIN fn_company bb ON aa._company_id=bb._company_id"
    dbAck=cur.execute(sql)
    dbInfos = cur.fetchmany(dbAck)

    for user in reportResult.userArray:
        for info in dbInfos:
            if int(user["userId"]) == int(info[0]):
                user["userName"] = info[1].encode("utf-8")
                user["userCode"] = info[2]
                user["companyId"] = str(info[3])
                user["companyName"] = info[4].encode("utf-8")

    #print(str(reportResult.userArray))
    #print("======================================================================")
    cur.close()
    conn.commit()
    conn.close()

    #step5: 统计数据的整理输出
    outputTxt(reportResult.userArray)

